package src.main.java.com.pms.service;



import src.main.java.com.pms.util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;

public class PetStatisticsService {
    /**
     * 计算平均年龄
     */
    public void calculateAverageAge() {
        String sql = "SELECT AVG(age) AS average_age FROM Pets WHERE deleted = 0";
        try (ResultSet rs = JDBCUtil.executeQueryWithConnection(sql)) {
            if (rs.next()) {
                System.out.println("平均年龄: " + rs.getDouble("average_age"));
            } else {
                System.out.println("未找到宠物记录。");
            }
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("错误: " + e.getMessage());
        }
    }


    /**
     * 查看最老宠物
     */
    public void viewOldestPet() {
        String sql = "SELECT id, name, type, age, created_at, updated_at, has_bath_appointment, has_vaccine_appointment, is_for_sale, asking_price, seller_contact, has_cremation_appointment FROM Pets WHERE deleted = 0 ORDER BY age DESC LIMIT 1";
        try (ResultSet rs = JDBCUtil.executeQueryWithConnection(sql)) {
            if (rs.next()) {
                System.out.println("最老的宠物 - ID: " + rs.getInt("id") +
                        ", 名字: " + rs.getString("name") +
                        ", 类型: " + rs.getString("type") +
                        ", 年龄: " + rs.getInt("age") +
                        ", 创建时间: " + rs.getString("created_at") +
                        ", 修改时间: " + rs.getString("updated_at") +
                        ", 洗澡预约状态: " + (rs.getInt("has_bath_appointment") == 1 ? "有未完成预约" : "无预约") +
                        ", 打疫苗预约状态: " + (rs.getInt("has_vaccine_appointment") == 1 ? "有未完成预约" : "无预约") +
                        ", 寄卖状态: " + (rs.getInt("is_for_sale") == 1 ? "正在寄卖" : "未寄卖") +
                        ", 寄卖期望价格: " + rs.getDouble("asking_price") +
                        ", 卖家联系方式: " + rs.getString("seller_contact") +
                        ", 火化预约状态: " + (rs.getInt("has_cremation_appointment") == 1 ? "有未完成预约" : "无预约")
                );
            } else {
                System.out.println("未找到宠物记录。");
            }
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("错误: " + e.getMessage());
        }
    }




    /**
     * 按年龄段统计宠物数量
     */
    public ResultSet countPetsByAgeRange() {
        String sql = "SELECT " +
                "CASE " +
                " WHEN age < 1 THEN '1岁以下' " +
                " WHEN age >= 1 AND age < 3 THEN '1-3岁' " +
                " WHEN age >= 3 AND age < 5 THEN '3-5岁' " +
                " WHEN age >= 5 AND age < 10 THEN '5-10岁' " +
                " ELSE '10岁以上' " +
                "END as age_range, " +
                "COUNT(*) as count " +
                "FROM Pets " +
                "WHERE deleted = 0 " +
                "GROUP BY age_range, age " +
                "ORDER BY MIN(age)";
        try {
            return JDBCUtil.executeQueryWithConnection(sql);
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("统计年龄段时发生错误: " + e.getMessage());
            return null;
        }
    }

    /**
     * 按类型统计宠物数量
     */
    public ResultSet countPetsByType() {
        String sql = "SELECT type, COUNT(*) AS count FROM Pets WHERE deleted = 0 GROUP BY type";
        try {
            return JDBCUtil.executeQueryWithConnection(sql);
        } catch (SQLException | ClassNotFoundException e) {
            System.out.println("错误: " + e.getMessage());
            return null;
        }
    }
}

